"""
    本SQL文件用于处理 迭代任务的 查询
"""
from common.constant import TEST_DEPARTMENT_ID

task_all_project_name_list_sql = """
select 
		id, `name`, `status`
FROM
		zt_project
WHERE
		type in ('program', 'project')
AND
		project=0
AND
        parent=0
AND
		deleted='0'

;"""

task_all_execution_name_list_sql = """
select 
    pj.id,
    name,
    pj.status
from
    zt_project as pj
where
    deleted = '0'
order by
    pj.begin
desc 
"""


task_all_task_name_list_sql = """
select 
    tsk.id,
    tsk.name,
    tsk.status
from
    zt_task as tsk
where
    deleted = '0'
# and
    # tsk.type = 'test'
order by
    tsk.openedDate
desc 
"""

department_person_info_list_sql = f"""
select 
	usr.id, 
	usr.realname as tester_name
from 
	zt_user as usr 
LEFT JOIN
	zt_dept as dpt 
on 
	dpt.id=usr.dept 
LEFT JOIN
	zt_group as grp 
ON
	grp.role = usr.role
WHERE
	usr.deleted = '0'
and
	SUBSTRING_INDEX(SUBSTRING_INDEX(dpt.path,',',-2),',',1) = {TEST_DEPARTMENT_ID}
"""


def construct_sql_task_detail_list(
    _project_id,
    _department_id,
    _iteration_id,
    _task_id,
    _task_status,
    _realname,
    _date_start_time,
    _date_end_time,
    _pageNo=0,
    _pageSize=25
):
    return f"""
select 
--     usrB.realname           as assign_user,
    usrB.realname           as creator,
    pj.id                   as project_id,
    pj.name                 as project_name,
    poj.id                  as execution_id,
    poj.name                as execution_name,
    tsk.name                as task_name,
    tsk.id                  as task_id,
    tsk.estimate            as task_estimate,
    tsk.consumed            as task_consumed,
    tsk.status              as task_status,
    eff.date                as log_date,
    ac.date                 as log_record_time,
    usr.realname            as log_recorder,
    eff.consumed            as log_consumed,
    eff.id                  as eff_id,
    eff.work                as log_content
from
    zt_effort as eff 
left join
    zt_task as tsk
on
    eff.objectID = tsk.id
left join
    zt_project as pj 
on
    pj.id = tsk.project 
left join
    zt_project as poj 
on 
    poj.id = tsk.execution
left join
    zt_user as usr 
on
    usr.account = eff.account 
left join
    zt_user as usrB 
on 
--     tsk.assignedTo = usrB.account 
    tsk.openedBy = usrB.account 
left join
    zt_action as ac
on
    ac.execution = eff.execution and ac.objectID = eff.objectID
and
    ac.comment = eff.work
LEFT JOIN 
    zt_dept as dpt
ON 
    dpt.id = usrB.dept
where
    tsk.deleted = '0'
{
    f'and pj.id = {_project_id}' if _project_id and _project_id > 0  else ''
}
{
    f'and poj.id = {_iteration_id}' if _iteration_id and _iteration_id > 0  else ''
}
{
    f'and (dpt.parent = {_department_id} or dpt.id = {_department_id})' if _department_id and _department_id > 0 else ''
}
{
    f'and tsk.id = {_task_id}' if _task_id and _task_id > 0  else ''
}
{
    f'and tsk.status = "{_task_status}"' if _task_status  else ''
}
{
    f'and usr.realname = "{_realname}"' if _realname else ''
}
{
    f'and eff.date >= Date("{_date_start_time}")' if _date_start_time else ''
}
{
    f'and eff.date < Date("{_date_end_time}")' if _date_end_time else ''
}
group by 
    eff.id
order by
    ac.date
desc
limit {_pageNo * _pageSize}, {_pageSize}
;"""


def construct_sql_task_detail_with_all(
    _project_id,
    _department_id,
    _iteration_id,
    _task_id,
    _task_status,
    _realname,
    _date_start_time,
    _date_end_time
):
    return f"""
select
    count(id) as total 
from
(
    select 
        distinct eff.id as id
    from
        zt_effort as eff 
    left join
        zt_task as tsk
    on
        eff.objectID = tsk.id
    left join
        zt_project as pj 
    on
        pj.id = tsk.project 
    left join
        zt_project as poj 
    on 
        poj.id = tsk.execution
    left join
        zt_user as usr 
    on
        usr.account = eff.account 
    left join
        zt_user as usrB 
    on 
    --     tsk.assignedTo = usrB.account 
        tsk.openedBy = usrB.account 
    left join
        zt_action as ac
    on
        ac.execution = eff.execution and ac.objectID = eff.objectID
    and
        ac.comment = eff.work
    LEFT JOIN 
        zt_dept as dpt
    ON 
    dpt.id = usrB.dept
    where
        tsk.deleted = '0'
    {
        f'and pj.id = {_project_id}' if _project_id and _project_id > 0 else ''
        }
    {
        f'and poj.id = {_iteration_id}' if _iteration_id and _iteration_id > 0 else ''
        }
    {
        f'and (dpt.parent = {_department_id} or dpt.id = {_department_id})' if _department_id and _department_id > 0 else ''
        }
    {
        f'and tsk.id = {_task_id}' if _task_id and _task_id > 0 else ''
        }
    {
        f'and tsk.status = "{_task_status}"' if _task_status else ''
        }
    {
        f'and usr.realname = "{_realname}"' if _realname else ''
        }
    {
        f'and eff.date >= Date("{_date_start_time}")' if _date_start_time else ''
        }
    {
        f'and eff.date < Date("{_date_end_time}")' if _date_end_time else ''
        }
) as tmp_table
;"""




def construct_sql_task_summary_list(
        _project_id,
        _iteration_id,
        _department_id,
        _task_id,
        _task_name,
        _task_type,
        _task_status,
        _task_deadline_begin,
        _task_deadline_end,
        _task_actual_start_date_begin,
        _task_actual_start_date_end,
        _task_finish_date_begin,
        _task_finish_date_end,
        _task_assign_to,
        _task_activate_date_begin,
        _task_activate_date_end,
        _page_no,
        _page_size
):
    return f"""
SELECT
	usr.realname 					as realname,
	pj.id                           as project_id,
	pj.name 						as project_name,
	poj.id 						    as execution_id,
	poj.name 						as execution_name,
	tsk.name 						as task_name,
	tsk.id 							as task_id,
	tsk.estimate 					as task_estimate,
	tsk.consumed 					as task_consumed,
	tsk.type 						as task_type,
	tsk.status 						as task_status,
	tsk.lastEditedDate 		        as task_last_edited_date,
	tsk.deadline 					as task_deadline,
	tsk.estStarted 					as task_plan_start, 
	tsk.realStarted 				as task_actual_start,
	"-" 							as task_plan_finish_date,
	tsk.finishedDate 				as task_finish_date,
	tsk.activatedDate 				as task_activate_date
FROM
	zt_task as tsk 
LEFT JOIN
	zt_project as pj 
ON
	pj.id = tsk.project
LEFT JOIN
	zt_project as poj 
ON
	poj.id = tsk.execution
LEFT JOIN	
	zt_user as usr 
ON
	usr.account = tsk.assignedTo
LEFT JOIN 
    zt_dept as dpt
ON 
    dpt.id = usr.dept
where 
    tsk.deleted = '0'
    {
        f'and pj.id = {_project_id}' if _project_id and _project_id > 0 else ''
        }
    {
        f'and poj.id = {_iteration_id}' if _iteration_id and _iteration_id > 0 else ''
        }
    {
        f'and (dpt.parent = {_department_id} or dpt.id = {_department_id})' if _department_id and _department_id > 0 else ''
        }
    {
        f'and tsk.id = {_task_id}' if _task_id and _task_id > 0 else ''
        }
    {
        f'and tsk.name like "%{_task_name}%"' if _task_name else ''
        }
    {
        f'and tsk.status = "{_task_status}"' if _task_status else ''
        }
    {
        f'and tsk.type = "{_task_type}"' if _task_type else ''
        }
    {
        f'and usr.realname = "{_task_assign_to}"' if _task_assign_to else ''
        }
    {
        f'and tsk.deadline >= "{_task_deadline_begin}"' if _task_deadline_begin else ''
        }
    {
        f'and tsk.deadline < "{_task_deadline_end}"' if _task_deadline_end else ''
        }
    {
        f'and tsk.realStarted >= "{_task_actual_start_date_begin}"' if _task_actual_start_date_begin else ''
        }
    {
        f'and tsk.realStarted < "{_task_actual_start_date_end}"' if _task_actual_start_date_end else ''
        }
    {
        f'and tsk.finishedDate >= "{_task_finish_date_begin}"' if _task_finish_date_begin else ''
        }
    {
        f'and tsk.finishedDate < "{_task_finish_date_end}"' if _task_finish_date_end else ''
        }
    {
        f'and tsk.activatedDate >= "{_task_activate_date_begin}"' if _task_activate_date_begin else ''
        }
    {
        f'and tsk.activatedDate < "{_task_activate_date_end}"' if _task_activate_date_end else ''
        }
order by
    tsk.lastEditedDate
desc
limit {_page_no * _page_size}, {_page_size}
;"""




def construct_sql_task_summary_list_with_all(
        _project_id,
        _iteration_id,
        _department_id,
        _task_id,
        _task_name,
        _task_type,
        _task_status,
        _task_deadline_begin,
        _task_deadline_end,
        _task_actual_start_date_begin,
        _task_actual_start_date_end,
        _task_finish_date_begin,
        _task_finish_date_end,
        _task_assign_to,
        _task_activate_date_begin,
        _task_activate_date_end
):
    return f"""
select
    count(id) as total 
from
(
    SELECT
        tsk.id
    FROM
        zt_task as tsk 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id = tsk.project
    LEFT JOIN
        zt_project as poj 
    ON
        poj.id = tsk.execution
    LEFT JOIN	
        zt_user as usr 
    ON
        usr.account = tsk.assignedTo
    LEFT JOIN 
        zt_dept as dpt
    ON 
        dpt.id = usr.dept
    where 
        tsk.deleted = '0'
        {
        f'and pj.id = {_project_id}' if _project_id and _project_id > 0 else ''
        }
    {
        f'and poj.id = {_iteration_id}' if _iteration_id and _iteration_id > 0 else ''
        }
    {
        f'and (dpt.parent = {_department_id} or dpt.id = {_department_id})' if _department_id and _department_id > 0 else ''
        }
    {
        f'and tsk.id = {_task_id}' if _task_id and _task_id > 0 else ''
        }
    {
        f'and tsk.name like "%{_task_name}%"' if _task_name else ''
        }
    {
        f'and tsk.status = "{_task_status}"' if _task_status else ''
        }
    {
        f'and tsk.type = "{_task_type}"' if _task_type else ''
        }
    {
        f'and usr.realname = "{_task_assign_to}"' if _task_assign_to else ''
        }
    {
        f'and tsk.deadline >= "{_task_deadline_begin}"' if _task_deadline_begin else ''
        }
    {
        f'and tsk.deadline < "{_task_deadline_end}"' if _task_deadline_end else ''
        }
    {
        f'and tsk.realStarted >= "{_task_actual_start_date_begin}"' if _task_actual_start_date_begin else ''
        }
    {
        f'and tsk.realStarted < "{_task_actual_start_date_end}"' if _task_actual_start_date_end else ''
        }
    {
        f'and tsk.finishedDate >= "{_task_finish_date_begin}"' if _task_finish_date_begin else ''
        }
    {
        f'and tsk.finishedDate < "{_task_finish_date_end}"' if _task_finish_date_end else ''
        }
    {
        f'and tsk.activatedDate >= "{_task_activate_date_begin}"' if _task_activate_date_begin else ''
        }
    {
        f'and tsk.activatedDate < "{_task_activate_date_end}"' if _task_activate_date_end else ''
        }
) as tmp_table

"""


assign_to_name_list_sql = """
SELECT
    usr.id as id,
	usr.realname as name
FROM
	zt_task as tsk 
LEFT JOIN
	zt_user as usr 
ON
	usr.account = tsk.assignedTo
WHERE
	usr.deleted = '0'
GROUP BY
	usr.account
ORDER BY
	usr.id
;"""


department_name_list_sql = """
SELECT 
    id, name
FROM
    zt_dept
WHERE 
    grade = 3
;"""
